﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace Dal
{
    public class DalIndex
    {
        SqlCommand cmd = null;
        string sql = "";
        public DataTable Nav_Name_All()
        {
            //所有一级导航显示
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            try
            {
                sql = "select * from Navigation where Id between 1 and 11 order by Sort asc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataTable Nav_Name_Display()
        {
            //展示的一级导航名单
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select * from Navigation where Show='true' order by Sort asc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataTable Module_Nav_Information()
        {
            //模块标题显示
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select * from Navigation where Show='true'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }
        public DataTable Module_Content_Information(ModelIndex m)
        {
            //模块内容显示
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select TOP 6 a.Id,a.NavId,a.NavSubId, a.Title,CONVERT(varchar(10), a.DateTime,120) as DateTime from Information as a left join Navigation as b on a.NavId=b.Id where b.Show='true' and b.Id='" + m.Id+"' and a.Deleted='false' and a.Release='True' order by a.DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }


        public DataTable Major_SubNav_Name_All()
        {
            //专业二级导航
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select Name,EnglishName from SubNavigation as a left join Major as b on a.Id=b.NavSubId where Recommend='True' and b.Deleted='False'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataTable Notice_Information()
        {
            //通知公告信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "SELECT TOP 10 Id,Title,_Content,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Author,Number,Important,Type FROM Notice where DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Deleted='false' and Release='true' ORDER BY ReleaseTime DESC";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                int a = dt.Rows.Count;
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataSet Retrieve_Information_All(ModelIndex m)
        {
            //检索全部
            //Search
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataSet ds = null;
            try
            {
                string sql1 = "select Id,NavId,NavSubId,Title,_Content,CONVERT(VARCHAR(10), DateTime,120) AS DateTime from Information where (Title like '%" + m.Title + "%' or _Content like '%" + m.Title + "%' or DateTime like '%" + m.Title + "%') and Deleted='false' and Release='true' ORDER BY DateTime DESC";

                string sql2 = "select Id,Title,_Content,CONVERT(VARCHAR(10), DateTime,120) AS DateTime,Important,Weekly,Type from Notice where (Title like '%" + m.Title + "%' or _Content like '%" + m.Title + "%' or DateTime like '%" + m.Title + "%') and Deleted='false' and Release='true' ORDER BY DateTime DESC";

                string sql3 = "select Id,NavId,NavSubId,Title,_Content,Author,CONVERT(VARCHAR(10), DateTime,120) AS DateTime from Major where (Title like '%" + m.Title + "%' or _Content like '%" + m.Title + "%' or DateTime like '%" + m.Title + "%') and Deleted='false' ORDER BY DateTime DESC";

                SqlCommand cmd1 = new SqlCommand(sql1, conn);
                SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
                ds = new DataSet();
                da1.Fill(ds, "table1");

                SqlCommand cmd2 = new SqlCommand(sql2, conn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataSet ds2 = new DataSet();
                da2.Fill(ds2, "table2");

                SqlCommand cmd3 = new SqlCommand(sql3, conn);
                SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
                DataSet ds3 = new DataSet();
                da3.Fill(ds3, "table3");

                ds2.Tables["table2"].Merge(ds3.Tables["table3"]);
                ds.Tables["table1"].Merge(ds2.Tables["table2"]);


                return ds;
            }
            catch (Exception)
            {
                ds = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return ds;
        }

        public bool Browse_Frequency(ModelIndex m)
        {
            //阅读次数增加
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                if (m.SqlId == 6)
                    sql = "update Information set Number=Number+1 where Id='" + m.Id + "'";
                if (m.SqlId == 2)
                    sql = "update Major set Number=Number+1 where NavSubId=(select Id from SubNavigation where Name='"+m.Name+"')";
                if (m.SqlId == 5)
                    sql = "update Notice set Number=Number+1 where Id='" + m.Id + "'";

                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//阅读次数增加

        public string SubNav_Name_Singlex(ModelIndex m)//这不也是PIECE里面的吗？？？
        {
            //显示二级导航名称
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "select Name from SubNavigation where Id=(select NavSubId from Information where Id='" + m.Id + "')";

                cmd = new SqlCommand(sql, conn);
                string name = cmd.ExecuteScalar().ToString();
                return name;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "";
        }
        public DataTable IndexImg2()//图片滚播
        {
            //
            //IndexImg2
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select TOP 6 Id,NavId,NavSubId,Title,_Content,CONVERT(varchar(10),DateTime,120) as DateTime,Author,Number,Release,Recommend,RecommendRoute,FileName,FileRoute,Remarks from Information where NavId=4 and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Release='True' and Recommend='True' and Deleted='false' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataTable SubNav_Name_All(ModelIndex m)
        {
            //二级全部
            //NavSubName
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                //if (m.Id == 12)
                //    m.NavId = "between 12 and 13";
                //else
                    m.NavId ="='"+ m.Id.ToString()+"'";

                sql = "select * from SubNavigation where BelongTo " + m.NavId + " order by Sort asc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataTable All_Information(ModelIndex m)
        {
            //信息
            //Information
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;

            try
            {
                string sql = "";
                if (m.SqlId == 1)//信息前三
                    sql = "select TOP 5 Id,NavId,NavSubId,Title,_Content,CONVERT(varchar(10),DateTime,120) as DateTime,Author,Number,Release,Recommend,RecommendRoute,FileName,FileRoute,Remarks from Information where NavSubId=(select Id from SubNavigation where name='" + m.Name + "') and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Release='true' and Deleted='false' order by DateTime desc";
                if (m.SqlId == 2)//指定的专业建设
                    sql = "select Id,NavId,NavSubId,Title,_Content,Author,CONVERT(varchar(10),DateTime,120) as DateTime,Number from Major where NavSubId=(select Id from SubNavigation where Name='" + m.Name + "') and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Deleted='false' and Recommend='True'";
                if (m.SqlId == 3)//通知公告全部
                    sql = "select Id,Title,_Content,CONVERT(varchar(10),DateTime,120)as DateTime,Author,Number,Important,Weekly,Type from Notice where DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Deleted='false' and Release='true' order by DateTime desc";
                if (m.SqlId == 4)
                    sql = "select Id,NavId,NavSubId,Title,CONVERT(varchar(10),DateTime,120)as DateTime from Information where NavId='" + m.NavId+ "' and NavSubId="+m.SubId+" and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Release='true' and Deleted='false' order by DateTime desc";
                if (m.SqlId == 5)
                    sql = "select Title,_Content,Author,Number,Weekly,CONVERT(varchar(10),DateTime,120)as DateTime,Type,FileName,FileRoute from Notice where Id='" + m.Id + "' and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Deleted='false'";
                if (m.SqlId == 6)//常规的数据内容显示
                    sql = "select Id,NavId,NavSubId,Title,_Content,CONVERT(varchar(10),DateTime,120) as DateTime,Author,Number,Release,Recommend,RecommendRoute,FileName,FileRoute,Remarks from Information where Id='" + m.Id + "' and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Release='true' and Deleted='false'";
                if (m.SqlId == 7)
                    sql = "select Id,Title,_Content from Notice where Id='"+m.Id+"' and DateTime<='"+DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")+"' and Deleted='False'";
                if (m.SqlId == 8)
                    sql = "select Id,Title,_Content from Information where Id='" + m.Id + "' and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' and Deleted='False'";
                if (m.SqlId == 9)
                    sql = "select Id,Title,_Content from Major where Id='" + m.Id + "' and DateTime<='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' and Deleted='False'";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;

            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        public DataTable Servise_All()
        {
            //服务指南
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select Id,NavId,Type,Adress,Title from Service where Deleted='false' and Release='True'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//服务指南
    }
}
